package service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import exceptions.ConnectionNotFoundException;
import exceptions.GenericSQLException;

import model.Teacher;

/**
 * Session Bean implementation class TeacherService
 */
@Stateless(mappedName = "teacherService")
@LocalBean
public class TeacherService {

    /**
     * Default constructor.
     */
    public TeacherService() {
    }

    /**
     * Vraag op of de teacher al geregistreerd is in de database
     *
     * @param teacher De Teacher
     * @return Het id van de teacher indien geregistreerd, anders -1.
     */
    public int isRegistered(Teacher teacher) {

        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet result = null;

        try {

            InitialContext ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/HRODevicesPool");
            conn = ds.getConnection();

            String query = "select id from teacher where code = ?";
            preparedStatement = conn.prepareStatement(query);
            preparedStatement.setString(1, teacher.getCode());
            result = preparedStatement.executeQuery();

            if (result.next()) {
                return result.getInt(1);
            }
            else {
                return -1;
            }
        }
        catch (NamingException e) {

            e.printStackTrace();

            throw new ConnectionNotFoundException("De databaseverbinding kan niet worden opgevraagd");
        }
        catch (SQLException e) {

            e.printStackTrace();

            throw new GenericSQLException("Er gaat iets mis met opvragen van de teacher uit de database.");
        }
        finally {

            try {

                if (result != null) {
                    result.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Registreer de teacher in de database en geef het id terug.
     *
     * @param teacher De teacher die geregistreerd moet worden.
     * @return Het id van het geregistreerde teacher.
     */
    public int registerTeacher(Teacher teacher) {

        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet generatedKeys = null;

        try {

            InitialContext ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/HRODevicesPool");
            conn = ds.getConnection();

            String query = "insert into teacher (name, code, present) values (?, ?, ?)";
            preparedStatement = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, "");
            preparedStatement.setString(2, teacher.getCode());
            preparedStatement.setBoolean(3, true);
            int affectedRows = preparedStatement.executeUpdate();

            if (affectedRows != 1) {
                throw new GenericSQLException("Er gaat iets mis met het opslaan van leraar: '" + teacher.getCode() + "' in de database.");
            }

            generatedKeys = preparedStatement.getGeneratedKeys();
            if (generatedKeys.next()) {

                return generatedKeys.getInt(1);
            }
            else {
                throw new GenericSQLException("Er gaat iets mis met het opslaan van leraar: '" + teacher.getCode() + "' in de database.");
            }
        }
        catch (NamingException e) {

            e.printStackTrace();

            throw new ConnectionNotFoundException("De databaseverbinding kan niet worden opgevraagd");
        }
        catch (SQLException e) {

            e.printStackTrace();

            throw new GenericSQLException("Er gaat iets mis met het opslaan van leraar: '" + teacher.getCode() + "' in de database.");
        }
        finally {

            try {

                if (generatedKeys != null) {
                    generatedKeys.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public ArrayList<Teacher> getAbsentTeachersByDevice(String deviceId) {

        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet result = null;

        try {

            ArrayList<Teacher> teachers = new ArrayList<Teacher>();

            InitialContext ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/HRODevicesPool");
            conn = ds.getConnection();

            String query = "select teacher.id, teacher.name, teacher.code from device left join device_has_teacher on device.id = device_has_teacher.device_id left join teacher on device_has_teacher.teacher_id = teacher.id where device.deviceid = ? and teacher.present = 0";
            preparedStatement = conn.prepareStatement(query);
            preparedStatement.setString(1, deviceId);
            result = preparedStatement.executeQuery();

            while (result.next()) {

                int id = result.getInt("id");
                String name = result.getString("name");
                String code = result.getString("code");

                Teacher teacher = new Teacher(id, name, code);
                teachers.add(teacher);
            }

            return teachers;
        }
        catch (NamingException e) {

            e.printStackTrace();

            throw new ConnectionNotFoundException("De databaseverbinding kan niet worden opgevraagd");
        }
        catch (SQLException e) {

            e.printStackTrace();

            throw new GenericSQLException("Er gaat iets mis met opvragen van de teachers uit de database.");
        }
        finally {

            try {

                if (result != null) {
                    result.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}
